USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'AuthenticateUser')
	BEGIN
		DROP  Procedure  dbo.AuthenticateUser
	END
GO

CREATE Procedure dbo.AuthenticateUser (
	@LOGINID				VARCHAR(100)
,	@PASSWORD				VARCHAR(100)
)
AS
BEGIN


	DECLARE @C_PARTNERCATEGORY	INT
	DECLARE @C_PARTNERTYPE		INT
	
	SET @C_PARTNERCATEGORY	= 1
	SET @C_PARTNERTYPE		= 4


	DECLARE @RESULT VARCHAR(MAX)
	SET @RESULT = '<RESPONSE><RESULT>$RESULT$</RESULT><MESSAGE><![CDATA[$MSG$]]></MESSAGE></RESPONSE>'

	declare @sql Varchar(max)
	set @sql = ''


	IF EXISTS(SELECT 1 FROM TR_USER WHERE LoginID = @LOGINID AND [Password] = @PASSWORD AND IsActive = 1)
	BEGIN
		SET @RESULT = REPLACE(@RESULT,'$RESULT$','S')		
		SET @RESULT = REPLACE(@RESULT,'$MSG$','')
	END
	ELSE IF EXISTS(SELECT 1 FROM TR_USER WHERE LoginID = @LOGINID AND [Password] = @PASSWORD AND IsActive = 0)
	BEGIN
		SET @RESULT = REPLACE(@RESULT,'$RESULT$','E')		
		SET @RESULT = REPLACE(@RESULT,'$MSG$','Inactive Account')
	END
	ELSE
	BEGIN
		SET @RESULT = REPLACE(@RESULT,'$RESULT$','E')		
		SET @RESULT = REPLACE(@RESULT,'$MSG$','Invalid Login ID or Password. Please try again')
	END

	;WITH FinalXML AS (
		SELECT 
			1 AS ID
		,	'<DATA>' AS RESULT
		UNION
		SELECT
			2 AS ID
		,	ISNULL(@RESULT,'') as RESULT
		UNION
		SELECT 
			3 AS ID
		,	'<USERID><![CDATA['+ CAST(p.PartnerNo AS VARCHAR)+']]></USERID>'  
		+	'<LASTNAME><![CDATA['+ ISNULL(p.PartnerLastName,'')+']]></LASTNAME>'
		+	'<FIRSTNAME><![CDATA['+ ISNULL(p.PartnerFirstName, '') + ']]></FIRSTNAME>'
		+	'<LOGINID><![CDATA['+ISNULL(u.LoginID,'')+']]></LOGINID>'
		+	'<MANAGERID><![CDATA['+ ISNULL( CAST(u.ManagerID AS VARCHAR),'')+']]></MANAGERID>'
		+	'<CANCREATEPARENTACCOUNT><![CDATA['+(CASE WHEN ISNULL(CanCreateParentAcct,0) = 1 THEN 'X' ELSE '' END ) + ']]></CANCREATEPARENTACCOUNT>'
		+	'<CANEDITPRODUCTMASTER><![CDATA['+(CASE WHEN ISNULL(CanEditProductMaster,0) = 1 THEN 'X' ELSE '' END )	+ ']]></CANEDITPRODUCTMASTER>'
		+	'<SUPERUSER><![CDATA['+(CASE WHEN ISNULL(isSuperUser,0) = 1 THEN 'X' ELSE '' END ) + ']]></SUPERUSER>'
		+	'<USERGUID><![CDATA['+ CAST(p.PartnerGUID AS VARCHAR(100))+ ']]></USERGUID>' 
				AS RESULT
		FROM TR_Partner as p WITH (NOLOCK)
		INNER JOIN TR_USER as u WITH (NOLOCK)
			ON u.PartnerGUID = p.PartnerGUID
		WHERE
			u.LoginID = @LOGINID
		AND u.Password = @PASSWORD
		UNION
		SELECT
			4 AS ID
		,	'</DATA>'
	
	)

	select @sql = @sql + RESULT
	FROM FinalXML
	ORDER BY ID


	select @sql

END
GO

-- AuthenticateUser 'user.two@company.com', 'user1'
